package gbench.appdemo.mall.erp;

import static gbench.common.tree.LittleTree.IRecord.REC;
import static gbench.common.tree.LittleTree.Jdbc.XJdbcSession.OPEN;
import static gbench.common.tree.LittleTree.Term.FT;

import java.time.LocalDateTime;
import java.util.Optional;
import java.util.UUID;
import java.util.function.BiFunction;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import gbench.common.matlib.data.DataReader.DFrame;
import gbench.common.tree.LittleTree.ExceptionalConsumer;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.Jdbc;
import gbench.common.tree.LittleTree.SQL;
import gbench.common.tree.LittleTree.Jdbc.XJdbcSession;

/**
 * 数据库的操作
 * @author gbench
 *
 */
public class MyDataApp{
    
    public MyDataApp(Jdbc jdbc){
        this.jdbc = jdbc;
    }
    
    /**
     * 事务函数
     * @param cs
     */
    public void withTransaction(ExceptionalConsumer<? super XJdbcSession<UUID, Object, DFrame>> cs) {
        jdbc.withTransaction(OPEN(DFrame.dfmclc,cs));
    }

    /**
     * 
     * @param sql sql 语句
     * @return IRecord 流
     */
    public Stream<IRecord> sqlqueryS(final String sql){
        return jdbc.sql2records(sql).stream();
    }

    /**
     * 
     * @param sql
     * @return
     */
    public Optional<IRecord> sql2maybe(final String sql){
        return jdbc.sql2maybe(sql);
    }

    /**
     * 
     * @param entityTable
     * @param itemTable
     * @param entityId
     * @return
     */
    public Stream<IRecord> relqueryR(final String entityTable, final String itemTable, final Integer entityId) {
        
        return this.rel2df_sql_f(entityTable, itemTable)
                .apply(FT("select * from tbl$ where entity_id=$0",entityId)).rowsS();
    }

    /**
     * 
     * @param entityTable
     * @param itemTable
     * @param entityId
     * @param itemId
     * @param drcr
     * @param quantity
     * @param optional
     */
    public void rel2insert(final String entityTable, final String itemTable, final Integer entityId, final Integer itemId, 
            final Integer drcr, final Double quantity, final Integer flag, final Number amount, final Number ref, final Object optional, final String description) {
        
            final var rec = REC( "optional", optional, 
                "drcr", drcr,
                "entity_id", entityId, 
                "item_id", itemId, 
                "quantity", quantity, 
                "amount", amount,
                "optional",optional,
                "flag", flag,
                "ref", ref,
                "description", description,
                "create_time", LocalDateTime.now());
            
            this.rel2insert_rec_f(entityTable, itemTable).apply(rec);
    }

    /**
     * 
     * @return  (tbl:表名,param:过滤的参数) -> stream
     */
    public BiFunction<String,IRecord,Stream<IRecord>> tbl2ss_sql_rec_bf(){
        return (tbl,params)->{
            final var where_line = params.stream(kvp->kvp.key()+"='"+kvp.value()+"'").collect(Collectors.joining(" and "));
            return jdbc.sql2records(FT("select * from $0",tbl,where_line.length()>0?where_line:"")).stream();
        };
    }
    
    /**
     * 
     * @return  (tbl:表名,param:过滤的参数) -> stream
     */
    public Function<IRecord,Stream<IRecord>> tbl2ss_rec_f(String tbl){
        return (params)->{
            final var where_line = params.stream(kvp->kvp.key()+"='"+kvp.value()+"'").collect(Collectors.joining(" and "));
            return jdbc.sql2records(FT("select * from $0",tbl,where_line.length()>0?where_line:"")).stream();
        };
    }

    /**
     * transaction中不能使用rel2insert_rec_f系列函数,会引发死锁,因为rel2insert_rec_f也会尝试锁表
     * @param tbl 表名
     * @return  (param:过滤的参数) -> maybe
     */
    public Function<IRecord,Optional<IRecord>> tbl2maybe_rec_f(final String tbl){
        return (params)->this.tbl2ss_sql_rec_bf().apply(tbl,params).findAny();
    }

    /**
     * 数据检索函数
     * @return  (tbl:表名,param:过滤的参数) -> maybe
     */
    public BiFunction<String,IRecord,Optional<IRecord>> tbl2maybe_tbl_rec_bf(){
        return (tbl,params)->this.tbl2ss_sql_rec_bf().apply(tbl,params).findAny();
    }

    /**
     * 实体明细查询: 明细表 占位符 tbl$
     * @param entityTbl 实体表
     * @param itemTbl 明细表
     * @return
     */
    public Function<String,DFrame> rel2df_sql_f(final String  entityTbl,final String itemTbl){
        final var tbl = FT("select * from t_entity_item_rel where name = '$0/$1'",entityTbl,itemTbl);
        return sql->{
            final var _sql = FT(sql,REC("tbl$","("+tbl+") tbl"));
            // println(_sql);
            final var d = jdbc.sql2records(_sql).stream().collect(DFrame.dfmclc);
            return d;
        };
    }
    
    /**
     * 实体明细查询: 明细表 占位符 tbl$
     * @param entityTbl 实体表
     * @param itemTbl 明细表
     * @return
     */
    public Function<String,Stream<IRecord>> rel2ss_sql_f(final String  entityTbl,final String itemTbl){
        final var tbl = FT("select * from t_entity_item_rel where name = '$0/$1'",entityTbl,itemTbl);
        return sql->{
            final var _sql = FT(sql,REC("tbl$","("+tbl+") tbl"));
            //println(_sql);
            final var d = jdbc.sql2records(_sql).stream();
            return d;
        };
    }
    
    /**
     * 实体明细查询: 明细表 占位符 tbl$
     * @param entityTbl 实体表
     * @param itemTbl 明细表
     * @return
     */
    public Function<IRecord,Stream<IRecord>> rel2ss_rec_f(final String  entityTbl,final String itemTbl){
        return rec->{
            final var sql = rec.stream(e->FT("$0='$1'",e._1(),e._2())).collect(Collectors.joining("and"));
            return this.rel2ss_sql_f(entityTbl, itemTbl).apply(sql);
        };
    }
    
    /**
     * 实体明细查询: 明细表 占位符 tbl$
     * @param entityTbl 实体表
     * @param itemTbl 明细表
     * @return
     */
    public Function<String,Optional<IRecord>> rel2maybe_sql_f(final String  entityTbl,final String itemTbl){
        return sql->{
            return this.rel2ss_sql_f(entityTbl, itemTbl).apply(sql).findAny();
        };
    }

    /**
     * 实体明细查询: 明细表 占位符 tbl$
     * @param entityTbl 实体表
     * @param itemTbl 明细表
     * @return
     */
    public Function<IRecord,Optional<IRecord>> rel2maybe_rec_f(final String  entityTbl,final String itemTbl){
        return rec->{
            final var where_line= rec.stream(e->FT("$0='$1'",e._1(),e._2())).collect(Collectors.joining(" and "));
            final var sql = FT("select * from tbl$ $0", rec.isEmpty()?"":FT("where $0",where_line));
            //println(sql);
            return this.rel2maybe_sql_f(entityTbl, itemTbl).apply(sql);
        };
    }

    /**
     * 
     * @param entityTable
     * @param itemTable
     * @param entityId
     * @param itemId
     * @param drcr
     * @param quantity
     * @param optional
     */
    public Function<IRecord,IRecord> rel2insert_rec_f(final String entityTable, final String itemTable ) {
        
        return rec -> jdbc.withTransaction(OPEN(DFrame.dfmclc, sess -> {
            final var entityId = rec.get("entity_id");
            final var itemId = rec.get("item_id");
            final var drcr = rec.get("drcr");
            final var quantity = rec.get("quantity");
            final var optional = rec.get("optional");
            final var amount = rec.get("amount");
            final var flag = rec.get("flag");
            final var ref = rec.get("ref");
            final var description = rec.str("description");
            final var r = REC("path", "D:/sliced/files/products/valuables/gold.jpg", 
                    "name", FT("$0/$1", entityTable, itemTable), 
                    "optional", optional, 
                    "drcr", drcr, 
                    "entity_id", entityId,
                    "item_id", itemId, 
                    "quantity", quantity, 
                    "amount",amount,
                    "flag",flag,
                    "ref",ref,
                    "description", description, 
                    "create_time", LocalDateTime.now());
            final var sql = SQL.of("t_entity_item_rel", r).insert();
            // println(sql);
            sess.sql2execute2int(sql);
        }));
    }

    private Jdbc jdbc;
    
}